"""
####=============
This program will be used to fill reputation the table created to compare migrated question from a website to questions 
posted on a website
##================="""


"""
@This file will extract data from all the 5 tables selected to plot  view count, answer count, comment count, 
answer count, reputation of users
"""
import os
import MySQLdb

"""
file_name = "5_sites_name"
database = "isec2014"
user = "root"
password="123"
port=3306
file_path2 = "parent_id" # These are the ids for which I am using their parent values
"""
file_name = "5_sites_name"
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
file_path2 = "parent_id" # These are the ids for which I am using their parent values
#"""


db1 = MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
get_cursor1 = db1.cursor()
get_cursor2 = db1.cursor()


db2 = MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
fill_cursor1 = db2.cursor()


site_name = open(file_name,"r")
for line in site_name:
    print "site=", line
    posts_table = "posts"+"_"+line.strip()+"_aug13"  
    users_table = "users"+"_"+line.strip()+"_aug13"  
    fill_table = "stat_"+line.strip()+"_aug13"
    
    q_str = "select id,score,answer_count, comment_count,view_count,owner_user_id from `"\
    +posts_table +"` where posttypeid = 1"# and id >= 15858463;"
    
    get_cursor1.execute(q_str)
    all_data =get_cursor1.fetchall()
    for data in all_data:  
        print "data=",data
        id = (str)(data[0])
        score =(str)( data[1])
        answer_count = (str)(data[2])
        comment_count = (str)(data[3])
        view_count =(str)( data[4])
        owner_user_id =(str) (data[5])


        user_str= "select id, display_name, reputation from `"+users_table+"` where id= "+owner_user_id
        #print "user str=", user_str
        get_cursor2.execute(user_str)
        users_data = get_cursor2.fetchall()
        #print "user data=", users_data
       
        owner_display_name=""
        reputation = ""
        if users_data:       
            owner_display_name=(str)( users_data[0][1])
            reputation = (str)(users_data[0][2])        
            fill_str = "insert into `"+fill_table +"` values("+id+","+score+","+view_count+","+answer_count+","+comment_count+\
            ","+owner_user_id+","+reputation+")"
        
            #print "fill_str=", fill_str 
            fill_cursor1.execute(fill_str)
        
            
           
    
    db2.commit()
db1.close()
db2.close()   